package mysql

/*
 * mysql ORM封装(add inore字段, 表示忽略其值)
 * 支持struct中嵌入struct字段直接按照json的方式存储在mysql对应的字段中，并能解析还原到struct对应的字段中。
 */
import (
	"database/sql"
	"encoding/json"
	"fmt"
	"reflect"
	"strconv"
	"strings"
)

// const flag.
const (
	gSQLTag     = "sql"
	gPrimaryTag = "primary"
	gIgnoreTag  = "ignore"
)

// mysql row define.
type mysqlRow map[string]interface{}

// build mysql result to rows.
func buildMysqlResultToRows(res *sql.Rows, rows *[]mysqlRow) error {
	cols, err := res.Columns()
	if err != nil {
		return err
	}
	rawResult := make([][]byte, len(cols))
	result := make([]interface{}, len(cols))
	for i := range result {
		result[i] = &rawResult[i]
	}

	for res.Next() {
		err := res.Scan(result...)
		if err != nil {
			return err
		}
		row := make(mysqlRow)
		for i, raw := range rawResult {
			if raw == nil {
				row[cols[i]] = nil
			} else {
				row[cols[i]] = string(raw)
			}
		}
		*rows = append(*rows, row)
	}
	return nil
}

// assignValue assign mysql value to field value.
func assignValue(fieldValue reflect.Value, mysqlValue interface{}) {
	if mysqlValue == nil {
		return
	}

	jsonString := fmt.Sprintf("%v", mysqlValue)
	switch fieldValue.Kind() {
	case reflect.String:
		fieldValue.SetString(jsonString)
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		intValue, err := strconv.ParseInt(jsonString, 10, 64)
		if err == nil {
			fieldValue.SetInt(intValue)
		}
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
		uintValue, err := strconv.ParseUint(jsonString, 10, 64)
		if err == nil {
			fieldValue.SetUint(uintValue)
		}
	case reflect.Float32, reflect.Float64:
		floatValue, err := strconv.ParseFloat(jsonString, 64)
		if err == nil {
			fieldValue.SetFloat(floatValue)
		}
	case reflect.Bool:
		boolValue, err := strconv.ParseBool(jsonString)
		if err == nil {
			fieldValue.SetBool(boolValue)
		}
	case reflect.Struct:
		if err := json.Unmarshal([]byte(jsonString), fieldValue.Addr().Interface()); err == nil {
			fieldValue.Set(reflect.ValueOf(fieldValue.Addr().Interface()).Elem())
		} else {
			fmt.Printf("JSON Unmarshal error: %v\n", err)
		}
	case reflect.Interface:
		fieldValue.Set(reflect.ValueOf(mysqlValue))
	default:
		fmt.Printf("Unhandled kind: %v\n", fieldValue.Kind())
	}
}

// QueryToStruct select query with args SQL TO dest object, where dest MUST be a array.
func QueryToStruct(client MysqlExecutorProxy, dest interface{}, query string, args ...interface{}) error {
	res, err := client.Query(query, args...)
	if err != nil {
		return err
	}
	defer res.Close()

	var rows []mysqlRow = make([]mysqlRow, 0, 16)
	if err := buildMysqlResultToRows(res, &rows); err != nil {
		return err
	}

	destValue := reflect.ValueOf(dest)
	if destValue.Kind() != reflect.Ptr || destValue.IsNil() {
		return fmt.Errorf("dest is not a valid pointer")
	}

	sliceValue := destValue.Elem()
	sliceType := sliceValue.Type().Elem()
	bElemPtr := sliceType.Kind() == reflect.Ptr
	if bElemPtr {
		sliceType = sliceType.Elem()
	}

	for _, row := range rows {
		elemValue := reflect.New(sliceType).Elem()

		for i := 0; i < sliceType.NumField(); i++ {
			field := sliceType.Field(i)
			// get sql tag
			if tag, tagOK := field.Tag.Lookup(gSQLTag); tagOK {
				if mysqlValue, ok := row[tag]; ok {
					fieldValue := elemValue.FieldByName(field.Name)
					if fieldValue.IsValid() && fieldValue.CanSet() {
						assignValue(fieldValue, mysqlValue)
					}
				}
			}
		}
		if bElemPtr {
			sliceValue = reflect.Append(sliceValue, elemValue.Addr())
		} else {
			sliceValue = reflect.Append(sliceValue, elemValue)
		}
	}
	destValue.Elem().Set(sliceValue)
	return nil
}

// get update column and values(supporting multi primary keys).
func getUpdateColumnsAndValues(dataValue reflect.Value, dataType reflect.Type, tag string) ([]string, []interface{}, []string, []interface{}) {
	var columns []string = make([]string, 0, 32)
	var values []interface{} = make([]interface{}, 0, 32)
	var primaryValues []interface{} = make([]interface{}, 0, 4)
	var primaryNames []string = make([]string, 0, 4)

	for i := 0; i < dataValue.NumField(); i++ {
		field := dataValue.Field(i)
		fieldType := dataType.Field(i)

		// 忽略没有指定列名的字段
		if tag, ok := fieldType.Tag.Lookup(tag); ok {
			if primary, ok := fieldType.Tag.Lookup(gPrimaryTag); ok && primary == "true" {
				primaryNames = append(primaryNames, "`"+tag+"`")
				primaryValues = append(primaryValues, field.Interface())
			} else {
				bIgnoreColumn := false
				ignore, ok := fieldType.Tag.Lookup(gIgnoreTag)
				if ok && ignore == "true" {
					str, ok := field.Interface().(string)
					if ok && str == "" {
						bIgnoreColumn = true
					}
				}

				if !bIgnoreColumn {
					var value interface{}
					if field.Kind() == reflect.Struct /*|| field.Kind() == reflect.Slice*/ {
						// 如果是嵌套的struct或切片，将其转换为JSON字符串
						jsonData, err := json.Marshal(field.Interface())
						if err != nil {
							continue
						}
						value = string(jsonData)
					} else {
						value = field.Interface()
					}
					columns = append(columns, fmt.Sprintf("`%s`=?", tag))
					values = append(values, value)
				}
			}
		}
	}

	return columns, values, primaryNames, primaryValues
}

// get insert column and values(supporting multi primary keys)
func getInsertColumnsAndValues(dataValue reflect.Value, dataType reflect.Type, tag string, primaryTag string, ignorePrimary bool) ([]string, []interface{}, []string, []interface{}) {
	var columns []string = make([]string, 0, 32)
	var values []interface{} = make([]interface{}, 0, 32)
	var primaryValues []interface{} = make([]interface{}, 0, 4)
	var primaryNames []string = make([]string, 0, 4)

	for i := 0; i < dataValue.NumField(); i++ {
		field := dataValue.Field(i)
		fieldType := dataType.Field(i)

		// 忽略没有指定列名的字段
		if tagValue, ok := fieldType.Tag.Lookup(tag); ok {
			if primary, ok := fieldType.Tag.Lookup(primaryTag); ok && primary == "true" {
				primaryNames = append(primaryNames, "`"+tagValue+"`")
				primaryValues = append(primaryValues, field.Interface())

				// 不忽略primary key的，才加入字段列表中。
				if !ignorePrimary {
					columns = append(columns, "`"+tagValue+"`")
					values = append(values, field.Interface())
				}
			} else {
				bIgnoreColumn := false
				ignore, ok := fieldType.Tag.Lookup(gIgnoreTag)
				if ok && ignore == "true" {
					str, ok := field.Interface().(string)
					if ok && str == "" {
						bIgnoreColumn = true
					}
				}

				if !bIgnoreColumn {
					if field.Kind() == reflect.Struct /*|| field.Kind() == reflect.Slice*/ {
						// 如果是嵌套的struct，将其转换为JSON字符串
						jsonData, err := json.Marshal(field.Interface())
						if err != nil {
							continue
						}
						columns = append(columns, "`"+tagValue+"`")
						values = append(values, string(jsonData))
					} else {
						columns = append(columns, "`"+tagValue+"`")
						values = append(values, field.Interface())
					}
				}
			}
		}
	}

	return columns, values, primaryNames, primaryValues
}

// insert操作:
// 返回lastId, affectCount和error.
// ignorePrimary表示是否忽略insert主键id，主要是针对那种自动增加id的primary key的情形。
func InsertStructToMysql(client MysqlExecutorProxy, tableName string, object interface{}, ignorePrimary bool) (int64, int64, error) {
	return InsertStruct(client, tableName, object, gSQLTag, ignorePrimary)
}
func InsertStruct(client MysqlExecutorProxy, tableName string, object interface{}, tag string, ignorePrimary bool) (int64, int64, error) {
	dataValue := reflect.Indirect(reflect.ValueOf(object))
	dataType := dataValue.Type()

	// 检查传入的数据是否是一个结构体指针
	if dataType.Kind() != reflect.Struct {
		return 0, 0, fmt.Errorf("data must be a struct pointer")
	}

	// 获取列名和值,忽略主键。
	columns, values, _, _ := getInsertColumnsAndValues(dataValue, dataType, tag, gPrimaryTag, ignorePrimary)

	// 执行INSERT语句
	query := fmt.Sprintf("INSERT INTO %s(%s) VALUES (%s)", tableName, strings.Join(columns, ","),
		strings.Repeat("?,", len(values)-1)+"?")
	if ret, err := client.Exec(query, values...); err != nil {
		return 0, 0, err
	} else {
		lastId, err1 := ret.LastInsertId()
		affectCount, err2 := ret.RowsAffected()
		if err1 != nil {
			return 0, 0, err1
		} else if err2 != nil {
			return 0, 0, err2
		} else {
			return lastId, affectCount, nil
		}
	}
}

// 插入多行数据
func getColumnAndPlaceholder(objectType reflect.Type, ignorePrimary bool) ([]string, string) {
	var columns []string = make([]string, 0, 32)
	var placeholders []string = make([]string, 0, 32)

	for i := 0; i < objectType.NumField(); i++ {
		fieldType := objectType.Field(i)

		// 忽略非导出字段
		if fieldType.PkgPath != "" {
			continue
		}

		// 忽略主键字段
		if fieldType.Tag.Get(gSQLTag) == gPrimaryTag && ignorePrimary {
			continue
		}

		// 获取列名,没有tag就忽略
		column := fieldType.Tag.Get(gSQLTag)
		if column == "" {
			continue
		}

		// 添加列名和占位符到对应的切片中
		columns = append(columns, "`"+column+"`")
		placeholders = append(placeholders, "?")
	}

	return columns, strings.Join(placeholders, ",")
}

// insert多行操作：
func getObjectValues(objectValue reflect.Value) []interface{} {
	var values []interface{} = make([]interface{}, 0, 32)

	for i := 0; i < objectValue.NumField(); i++ {
		field := objectValue.Field(i)
		fieldType := objectValue.Type().Field(i)

		// 忽略非导出字段
		if fieldType.PkgPath != "" {
			continue
		}

		// 获取列名,没有tag就忽略
		column := fieldType.Tag.Get(gSQLTag)
		if column == "" || column == "-" {
			continue
		}

		// 获取字段值
		value := field.Interface()

		// 添加值到对应的切片中
		values = append(values, value)
	}

	return values
}
func InsertMultiObjects(client MysqlExecutorProxy, tableName string, objects interface{}, ignorePrimary bool) error {
	// 检查传入的数据是否是一个结构体指针切片
	objectsValue := reflect.ValueOf(objects)
	if objectsValue.Kind() != reflect.Slice {
		return fmt.Errorf("objects must be a slice")
	}

	objectType := objectsValue.Type().Elem()
	if objectType.Kind() != reflect.Struct {
		return fmt.Errorf("objects must be a slice of structs")
	}

	// 获取列名和占位符
	columns, placeholders := getColumnAndPlaceholder(objectType, ignorePrimary)

	// 构造多行插入语句的占位符
	multiPlaceholders := strings.Repeat(fmt.Sprintf("(%s),", placeholders), objectsValue.Len()-1) + fmt.Sprintf("(%s)", placeholders)

	// 构造多行插入语句
	query := fmt.Sprintf("INSERT INTO %s(%s) VALUES %s", tableName, strings.Join(columns, ","), multiPlaceholders)

	// 构造参数列表
	var values []interface{} = make([]interface{}, 0, objectsValue.Len())
	for i := 0; i < objectsValue.Len(); i++ {
		objectValue := reflect.Indirect(objectsValue.Index(i))
		objectValues := getObjectValues(objectValue)
		values = append(values, objectValues...)
	}

	// 执行多行插入语句
	if _, err := client.Exec(query, values...); err != nil {
		return err
	}
	return nil
}

// update操作：
// 必须带primary更新。
// 其中data是对象的指针类型
func UpdateStructToMysql(client MysqlExecutorProxy, tableName string, object interface{}) (int64, error) {
	return UpdateStruct(client, tableName, object, gSQLTag)
}
func UpdateStruct(client MysqlExecutorProxy, tableName string, object interface{}, tag string) (int64, error) {
	dataValue := reflect.Indirect(reflect.ValueOf(object))
	dataType := dataValue.Type()

	// 检查传入的数据是否是一个结构体指针
	if dataType.Kind() != reflect.Struct {
		return 0, fmt.Errorf("data must be a struct pointer")
	}

	// 获取列名和值
	columns, values, primaryNames, primaryValues := getUpdateColumnsAndValues(dataValue, dataType, tag)

	// 检查是否有更新的列
	if len(columns) == 0 {
		return 0, fmt.Errorf("no columns to update")
	}

	// 构建WHERE子句
	var whereConditions []string = make([]string, 0, len(primaryNames))
	for i := 0; i < len(primaryNames); i++ {
		whereConditions = append(whereConditions, fmt.Sprintf("%s=?", primaryNames[i]))
	}

	// 构建一个假条件。
	if len(whereConditions) == 0 {
		whereConditions = append(whereConditions, "1=1")
	}
	whereClause := fmt.Sprintf("WHERE %s", strings.Join(whereConditions, " AND "))

	// 执行UPDATE语句
	query := fmt.Sprintf("UPDATE %s SET %s %s", tableName, strings.Join(columns, ","), whereClause)
	values = append(values, primaryValues...)
	if ret, err := client.Exec(query, values...); err != nil {
		return 0, err
	} else {
		return ret.RowsAffected()
	}
}

// 自动实现转移一个struct结构的string字段
// Escape the string field for the struct.
func EscapeStructField(data interface{}) {
	v := reflect.ValueOf(data)
	if v.Kind() == reflect.Ptr {
		v = v.Elem()
	}
	if v.Kind() != reflect.Struct {
		return
	}

	for i := 0; i < v.NumField(); i++ {
		field := v.Field(i)
		if field.Kind() == reflect.String {
			value := field.String()
			field.SetString(string(EscapeString(value)))
		}
	}
}

// 自动将一个struct的字符串字段的前后空格删除。
// Trim the space for all the string field of the struct.
func TrimStructStringField(data interface{}) {
	v := reflect.ValueOf(data)
	if v.Kind() == reflect.Ptr {
		v = v.Elem()
	}
	if v.Kind() != reflect.Struct {
		return
	}

	for i := 0; i < v.NumField(); i++ {
		field := v.Field(i)
		if field.Kind() == reflect.String {
			value := field.String()
			field.SetString(strings.TrimSpace(value))
		}
	}
}
